package lib_model.Product;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import lib_dal.Connection.ConnectMSSQL;
import lib_dal.Interface.ISql;
import lib_model.Orther.ReturnObj;
import lib_model.WareHouse.Stocks_warehouse;

public class Products implements ISql {

	public int id;
	public int categories_id_ref;
	public int tax_product_id_ref;
	public String username_ref;
	public String name;
	public float price;
	public String description;
	public Date date_create;
	public Date date_update;
	public int type;

	public Products() {
	}

	public Products(int id) {
		super();
		this.id = id;
	}
	
	public int quantity(){
		Stocks_warehouse wh = new Stocks_warehouse();
		ArrayList<Stocks_warehouse> sth = (ArrayList<Stocks_warehouse>) wh.Select().ObjResult;
		int totalquan = 0;
		for(Stocks_warehouse ww : sth){
			if(id == ww.products_id_ref){
				totalquan += ww.quantity;
			}
		}
		return totalquan;
	}

	@Override
	public ReturnObj Select() {
		String sql = "select * from dbo.products order by id desc";
		ReturnObj obj = new ReturnObj();
		ArrayList<Products> arr = new ArrayList<Products>();
		try {
			PreparedStatement preState = new ConnectMSSQL()
					.preparedStatement(sql);
			ResultSet rs = preState.executeQuery();
			while (rs.next()) {
				Products a = new Products();
				a.id = rs.getInt("id");
				a.categories_id_ref = rs.getInt("categories_id_ref");
				a.tax_product_id_ref = rs.getInt("tax_product_id_ref");
				a.username_ref = rs.getString("username_ref");
				a.name = rs.getString("name");
				a.price = rs.getFloat("price");
				a.description = rs.getString("description");
				a.date_create = rs.getDate("date_create");
				a.date_update = rs.getDate("date_update");
				a.type = rs.getInt("type");
				arr.add(a);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		obj.ObjResult = arr;
		return obj;
	}

	@Override
	public ReturnObj Add() {
		String sql = "insert into dbo.products ("
				+ "categories_id_ref,   "
				+ "tax_product_id_ref,   "
				+ "username_ref,   "
				+ "name,   "
				+ "price,   "
				+ "description,   "
				+ "date_create,  "
				+ "type) values   (?,   ?,   ?,   ?,   ?,   ?,   GETDATE(),   ?)";
		ReturnObj obj = new ReturnObj();
		int key = 0;
		try {
			PreparedStatement preState = new ConnectMSSQL()
					.preparedStatement(sql);
			preState.setInt(1, this.categories_id_ref);
			preState.setInt(2, this.tax_product_id_ref);
			preState.setString(3, this.username_ref);
			preState.setString(4, this.name);
			preState.setFloat(5, this.price);
			preState.setString(6, this.description);
			preState.setInt(7, this.type);
			obj.Result = preState.execute();
			ResultSet keys = preState.getGeneratedKeys();
			keys.next();
			key = keys.getInt(1);
			keys.close();
			preState.close();
			obj.ObjResult = key;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return obj;
	}

	@Override
	public ReturnObj Update() {
		String sql = "update dbo.products set categories_id_ref = ?,   tax_product_id_ref = ?,   username_ref = ?,   name = ?,   price = ?,   description = ?, date_update = GETDATE(),type = ? where  id = ?";
		ReturnObj obj = new ReturnObj();
		try {
			PreparedStatement preState = new ConnectMSSQL()
					.preparedStatement(sql);
			preState.setInt(1, this.categories_id_ref);
			preState.setInt(2, this.tax_product_id_ref);
			preState.setString(3, this.username_ref);
			preState.setString(4, this.name);
			preState.setFloat(5, this.price);
			preState.setString(6, this.description);
			preState.setInt(7, this.type);
			preState.setInt(8, this.id);
			obj.Result = (preState.executeUpdate() == 1) ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return obj;
	}

	@Override
	public ReturnObj Delete() {
		String sql = "delete from dbo.products where id = ?";
		ReturnObj obj = new ReturnObj();
		try {
			PreparedStatement preState = new ConnectMSSQL()
					.preparedStatement(sql);
			preState.setInt(1, this.id);
			preState.execute();
			obj.Result = preState.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return obj;
	}

	@Override
	public ReturnObj GetInformation() {
		ReturnObj obj = new ReturnObj();
		String sql = "select id, categories_id_ref, tax_product_id_ref, username_ref, name, price, description, date_create, date_update,type from dbo.products where id = ?";
		try {
			PreparedStatement preState = new ConnectMSSQL()
					.preparedStatement(sql);
			preState.setInt(1, this.id);
			ResultSet rs = preState.executeQuery();
			while (rs.next()) {
				Products a = new Products();
				a.id = rs.getInt("id");
				a.categories_id_ref = rs.getInt("categories_id_ref");
				a.tax_product_id_ref = rs.getInt("tax_product_id_ref");
				a.username_ref = rs.getString("username_ref");
				a.name = rs.getString("name");
				a.price = rs.getFloat("price");
				a.description = rs.getString("description");
				a.date_create = rs.getDate("date_create");
				a.date_update = rs.getDate("date_update");
				a.type = rs.getInt("type");
				obj.ObjResult = a;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return obj;
	}
	public ReturnObj SelectToNew() {
		String sql = "select top (20) * from dbo.products order by date_create desc";
		ReturnObj obj = new ReturnObj();
		ArrayList<Products> arr = new ArrayList<Products>();
		try {
			PreparedStatement preState = new ConnectMSSQL()
					.preparedStatement(sql);
			ResultSet rs = preState.executeQuery();
			while (rs.next()) {
				Products a = new Products();
				a.id = rs.getInt("id");
				a.categories_id_ref = rs.getInt("categories_id_ref");
				a.tax_product_id_ref = rs.getInt("tax_product_id_ref");
				a.username_ref = rs.getString("username_ref");
				a.name = rs.getString("name");
				a.price = rs.getFloat("price");
				a.description = rs.getString("description");
				a.date_create = rs.getDate("date_create");
				a.date_update = rs.getDate("date_update");
				a.type = rs.getInt("type");
				arr.add(a);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		obj.ObjResult = arr;
		return obj;
	}
	public ReturnObj GetByName(String name) {
		ReturnObj obj = new ReturnObj();
		String sql = "select * from dbo.products where name = ?";
		try {
			PreparedStatement preState = new ConnectMSSQL()
					.preparedStatement(sql);
			preState.setString(1, name);
			ResultSet rs = preState.executeQuery();
			while (rs.next()) {
				Products a = new Products();
				a.id = rs.getInt("id");
				a.categories_id_ref = rs.getInt("categories_id_ref");
				a.tax_product_id_ref = rs.getInt("tax_product_id_ref");
				a.username_ref = rs.getString("username_ref");
				a.name = rs.getString("name");
				a.price = rs.getFloat("price");
				a.description = rs.getString("description");
				a.date_create = rs.getDate("date_create");
				a.date_update = rs.getDate("date_update");
				a.type = rs.getInt("type");
				obj.ObjResult = a;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return obj;
	}
	
	
	public ReturnObj SelectByIDCategories(int idcate) {
		System.out.println(idcate);
		String sql = "select * from dbo.products where categories_id_ref = " + idcate + " order by id desc";
		ReturnObj obj = new ReturnObj();
		ArrayList<Products> arr = new ArrayList<Products>();
		try {
			PreparedStatement preState = new ConnectMSSQL()
					.preparedStatement(sql);
			ResultSet rs = preState.executeQuery();
			while (rs.next()) {
				Products a = new Products();
				a.id = rs.getInt("id");
				a.categories_id_ref = rs.getInt("categories_id_ref");
				a.tax_product_id_ref = rs.getInt("tax_product_id_ref");
				a.username_ref = rs.getString("username_ref");
				a.name = rs.getString("name");
				a.price = rs.getFloat("price");
				a.description = rs.getString("description");
				a.date_create = rs.getDate("date_create");
				a.date_update = rs.getDate("date_update");
				a.type = rs.getInt("type");
				arr.add(a);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		obj.ObjResult = arr;
		return obj;
	}
}
